
Create database Test 
go
use Test 
go 
create table Student
(
	ID int,
	Name nvarchar(20)
)
go
insert into Student 
select 1,'fang'
union all
select 2,'Guan'
union all
select 3,'Lin'

select * from Student





USE ATE
go 

declare @temp table(PackageProductSN nvarchar(50),ProductSN nvarchar(50));
insert into @temp 
select PackageProductSN,ProductSN 
from PackageTest pt
where IsPackage='Yes' 
and PackageTime between DATEADD(dd, DATEDIFF(dd,1,getdate()), 0) and DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)
and Exists(select * from ProductType_ZTE pz where pz.ProductTypeID=pt.ProductTypeID);

select t.PackageProductSN,tdi.* 
from tb_DFL_Info tdi
inner join @temp t 
on tdi.partcode=t.ProductSN;  

select t.PackageProductSN,trd.*
from tb_result_DFL trd
inner join @temp t
on trd.partcode=t.ProductSN; 

select t.PackageProductSN,tpd.*
from tb_Param_DFL tpd
inner join @temp t
on tpd.partcode=t.ProductSN; 
Go





declare @temp table(PackageProductSN nvarchar(50),resultid int,partcode nvarchar(50));
insert into @temp 
select pt.PackageProductSN,trd.resultid,trd.partcode  
from PackageTest pt 
inner join tb_result_DFL trd 
on pt.ProductSN=trd.partcode 
where IsPackage='Yes' and trd.totalresult='0'
and PackageTime between DATEADD(dd, DATEDIFF(dd,1,getdate()), 0) and DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)
and Exists(select * from ProductType_ZTE pz where pz.ProductTypeID=pt.ProductTypeID);

select t.PackageProductSN,tdi.* 
from tb_DFL_Info tdi 
inner join @temp t 
on tdi.resultid=t.resultid and tdi.partcode=t.partcode;  

select t.PackageProductSN,trd.*
from tb_result_DFL trd
inner join @temp t
on trd.resultid=t.resultid and trd.partcode=t.partcode; 

select t.PackageProductSN,tpd.* 
from tb_Param_DFL tpd 
inner join @temp t 
on tpd.resultid=t.resultid and tpd.partcode=t.partcode; 
Go






























